package com.cunyu.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

import javax.annotation.PostConstruct;

import cn.tom.kit.StringUtil;
import com.cunyu.bean.Form;
import com.cunyu.bean.Muser;
import com.cunyu.bean.ReqData;
import com.cunyu.bean.RespData;
import com.cunyu.dao.DBQuery;
import com.cunyu.util.MD5;
import com.cunyu.util.ObjectId;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

@Service
public class MuserService extends BaseService {
	
	@PostConstruct
	void checkConn() throws SQLException {
		Connection conn = getConn();
		DatabaseMetaData mdm = conn.getMetaData();
		logger.info("connect database success: " + mdm.getDatabaseProductName() + mdm.getDatabaseProductVersion());
		conn.close();
	}
	
	
	/*public boolean checktel(String tel, String pname) {
		String sql = "select count(*) from myrel where tel =? and pname =?";
		int cnt =  super.getInt(sql, Long.parseLong(tel), pname);
		return cnt==0;
	}*/

	public boolean checkname(String tel) {
		return true;
	}

	public Muser addUser(Muser user) {
		return null;
	}

	public Muser hashLogin(Muser user) {
		Muser _user = super.getBean("select * from muser a where tel = ?", Muser.class, user.getTel());
		if (_user == null)
			return null;
		if (_user.getPassword().equals(MD5.MD5Encode(user.getPassword()))) {
			return _user;
		}
		return null;
	}

	public Muser getMuser(long token) {
		return super.getBean("select * from muser a where token = ?", Muser.class, token);
	}
	
	/*public List<Map<String, Object>> getMenu(Muser user, int pid) {
		String sql = "select * from muserrole where role like ? and pid =? and state =0";
		List<Map<String, Object>> list = super.getList(sql, "%" + user.getRole() + "%", pid);
		for (Map<String, Object> map : list) {
			List<Map<String, Object>> child = getMenu(user, (int) map.get("id"));
			if (pid > 0) { // child.isEmpty(); 只读2层, 因为velocity值支持两层
				return list;
			} else {
				map.put("child", child);
			}
		}
		return list;
	}*/

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getMenuOne(Muser user ) {
		String sql = "select * from muserrole where role like ? and state =0 order by pid ,id";
		List<Map<String, Object>> list = getList(sql, "%[" + user.getRole() + "]%");
		// format to a tree
		Map<String,Map<String,Object>> table = new LinkedHashMap<>();
		for (Map<String,Object> unit: list){
			String id = unit.get("id").toString();
			String pid = unit.get("pid").toString();
			if("0".equals(pid) ){
				table.put(id, unit) ;
				continue ;
			}
			if(table.get(pid) == null){
				continue ;
			}
			Object child = table.get(pid).get("child");
			if(child == null){
				child = new ArrayList<>() ;
				table.get(pid).put("child",child) ;
			}
			((List)child).add(unit) ;
		}
		return  new ArrayList<>(table.values());
	}


	/**
	 * 用户 app日志
	 * @param reqData
	 * @param form
	 * @return
	 */
	public RespData<List<Map<String, Object>>> userlog(ReqData reqData, Form form) {
		String sql = "select a.tel,c.name, b.* from suser a  join userinfo c on a.id = c.id join userlog b on a.id =b.uid where 1=1";
		if (StringUtils.hasLength(form.getTel())) {
			sql += " and a.tel=" + form.getTel();
		}

		if (StringUtils.hasLength(form.getBegintime())) {
			sql += " and b.ctime >'" + form.getBegintime() + "'";
		}

		if (StringUtils.hasLength(form.getEndtime())) {
			sql += " and b.ctime <'" + form.getEndtime() + "'";
		}

		if (StringUtils.hasLength(form.getState())) {
			sql += " and b.state =" + form.getState();
		}

		return super.page(sql, reqData);
	}


	@Transactional(rollbackFor = Exception.class)
	public void addOne(Form form) {
		long tel = Long.parseLong(form.getTel()); 
		
		String sql ="insert into muser(tel,uname,role,token,password)values(?,?,?,?,?)";
		
		int id = super.insert(sql, tel, form.getName(), Integer.parseInt(form.getRole()),
				new ObjectId().toLong(), MD5.MD5Encode(form.getPassword()));
		
		sql = "insert into muserinfo(muid, rel) values(?,?)";
		super.exec(sql, id, form.getPname());
	}
	

	public RespData<List<Map<String, Object>>> list(ReqData reqData, Form form) {
		String tel = form.getTel();
		String name = form.getName();
		String rel = form.getPname();
		String role = form.getRole();

		String sql = "select id,tel,uname,role,ctime, (select gname from mgroup where role = a.role), "
				+ "rel, cnt from muser a join muserinfo b on a.id = b.muid ";
		if(StringUtils.hasLength(role)){
			sql += " and role = "+role;
		}
		if(StringUtils.hasLength(rel)){
			sql += " and rel = '"+rel+"'";
		}
		if(StringUtils.hasLength(name)){
			sql += " and name = '"+name+"'";
		}
		if(StringUtils.hasLength(tel)){
			sql += " and tel = "+tel;
		}
		return super.page(sql, reqData);
	}
	
	
	public RespData<List<Map<String, Object>>> relcnt(ReqData reqData, Form form) {
		String pname = form.getPname();
		String name = form.getName();
		String begintime = form.getBegintime();
		String endtime = form.getEndtime();
		reqData.setSort(null);
		String where  = "";
		if(StringUtils.hasLength(pname)){
			where += " and pname = '"+pname+"'";
		}
		if(StringUtils.hasLength(name)){
			where += " and name = '"+name+"'";
		}
		if(StringUtils.hasLength(begintime)){
			where += " and ctime >= '"+begintime+"'";
		}
		if(StringUtils.hasLength(endtime)){
			where += " and ctime <= '"+endtime+"'";
		}
		String sql = "select pname,name,count(*) from rel where 1=1" +where+" group by pname,name";
		return super.page(sql, reqData);
	}

	public RespData<List<Map<String, Object>>> getUsers(ReqData page, Form form) {

		String sql = "select mu.*,(select gname from mgroup r where r.role = mu.role ) as gname , " +
				"(select p.uname from muser p where p.id = mu.pid ) as pname, " +
				"(select qcode from muserinfo where muid = mu.id) as qcode from muser mu where 1 = 1 " ;

		DBQuery query = new DBQuery(sql) ;
		if(StringUtil.hasLength(form.getTel())){
			query.and(" mu.tel = ? ").setParams(Long.valueOf(form.getTel()));
		}
		if(StringUtil.hasLength(form.getUname())){
			query.and(" mu.uname like ? ").setParams("%"+form.getUname()+"%");
		}
		if(StringUtil.hasLength(form.getPid())){
			query.and(" mu.pid = ? ").setParams(Integer.valueOf(form.getPid())) ;
		}
		if(StringUtil.hasLength(form.getRole())){
			query.and(" mu.role = ? ").setParams(Integer.valueOf(form.getRole())) ;
		}
		if(StringUtil.hasLength(form.getState())){
			query.and(" mu.state = ? ").setParams(Integer.valueOf(form.getState())) ;
		}
		return page(query.getSql(), page,query.getParams());
	}

	public List<Map<String, Object>> getPusers() {
//		String sql = "select id,uname from muser where pid = 1 or id = 1 " ;
		String sql = "select id,uname from muser" ;
		return getList(sql) ;
	}

	/**
	 * 改变状态时 修改 token
	 * */
	public int upState(String id, String state) {
		String sql = "update muser set state = ?,token = ? where id = ? " ;
		int exec = exec(sql, Integer.valueOf(state),ObjectId.get().toLong(),Integer.valueOf(id));
		return exec ;
	}

    public Map<String, Object> getUser(String id) {
		String sql = "select * ,(select qcode from muserinfo where muid = id) as qcode" +
				" from muser where id = ? " ;
		return getMap(sql,Integer.valueOf(id)) ;
    }

    /**
	 * 修改用户信息，改密码时 将token 改掉
	 * */
	public int updateUser(Form form) {
		String sql = "update muser set uname=?,tel=?,state=?,role=?,img=?,pid =?" ;
		DBQuery query = new DBQuery(sql ) ;
		query.setParams(form.getUname()) ;
		query.setParams(Long.valueOf(form.getTel())) ;
		query.setParams(Integer.valueOf(form.getState())) ;
		query.setParams(Integer.valueOf(form.getRole())) ;
		query.setParams(form.getImg()) ;
		query.setParams(Integer.valueOf(form.getPid())) ;
		if(StringUtil.hasLength(form.getPassword())){
			query.add(" ,password=?").setParams(MD5.MD5Encode(form.getPassword())) ;
			// 并将 token 改掉
            query.add(" ,token = ? ").setParams(ObjectId.get().toLong()) ;
		}
		query.add(" where id = ? ").setParams(Integer.valueOf(form.getId())) ;

		String qcodeSql = "update muserinfo set qcode = ? where muid = ? " ;
		exec(query.getSql(),query.getParams()) ;
		exec(qcodeSql,form.getQcode(),Integer.valueOf(form.getId())) ;
		return 2;
	}

	public int newUser(Form form) {
		String sql = "insert into muser (uname,tel,state,role,img,pid,ctime,password,token) values" +
				" (?,?,?,?,?,?,?,?,?)" ;
		int id = insert (sql,form.getUname(),Long.valueOf(form.getTel()),Integer.valueOf(form.getState()),intV(form.getRole()),
				form.getImg(),intV(form.getPid()),new Timestamp(System.currentTimeMillis()),
				MD5.MD5Encode(form.getPassword()),ObjectId.get().toLong());
		String qcodeSql = "insert into muserinfo (muid,qcode) values (?,?)" ;
		insert(qcodeSql,id,form.getQcode());
		return id ;
	}

    public List<Map<String, Object>> getNameList() {
		String sql = "select uname,id from muser " ;
		return getList(sql) ;
    }

    public int hasQcode(String qcode) {
		String sql = "select muid from mUserInfo where qcode = ? " ;
    	return getInt(sql,qcode);
 	}
}
